package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.flex.remoting.RemotingInclude;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.CorpPublication;
import com.pagepub.expo.entity.IdName;

@Repository
public class CorpPublicationDao  {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;
	
	private static String tableName="Corp_Publication";
	
	private static String ID_NAME="SELECT id, title name FROM "+tableName;
	
	private static String QUERY_ALL = "SELECT id,typeId,corpId,title,thumbPath," +
			"width,height,backgroundSound,backgroundRepeat,backgroundAlpha," +
			"sounLoop,password,hits,comments,isAuditing," +
			"createDate,modDate,endPath,background,adWidth," +
			"adHeight,pagesCount,pubmemSize,isValid,isCommend," +
			"pubPath,pubType,pubMagazineCount,pubZoom,pubBackgroudSize," +
			"pubadSize,pwdLapseDate,pwdStartPage,isCommpany FROM "+tableName;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
			"id,typeId,corpId,title,thumbPath,"+
			"width,height,backgroundSound,backgroundRepeat,backgroundAlpha," +
			"sounLoop,password,hits,comments,isAuditing," +
			"createDate,modDate,endPath,background,adWidth," +
			"adHeight,pagesCount,pubmemSize,isValid,isCommend," +
			"pubPath,pubType,pubMagazineCount,pubZoom,pubBackgroudSize," +
			"pubadSize,pwdLapseDate,pwdStartPage,isCommpany)VALUES(" +
			":id,:typeId,:corpId,:title,:thumbPath," +
			":width,:height,:backgroundSound,:backgroundRepeat,:backgroundAlpha," +
			":sounLoop,:password,:hits,:comments,:isAuditing," +
			":createDate,:modDate,:endPath,:background,:adWidth," +
			":adHeight,:pagesCount,:pubmemSize,:isValid,:isCommend," +
			":pubPath,:pubType,:pubMagazineCount,:pubZoom,:pubBackgroudSize," +
			":pubadSize,:pwdLapseDate,:pwdStartPage,:isCommpany)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE id=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName + " SET " +
			"typeId=:typeId,corpId=:corpId,title=:title,thumbPath=:thumbPath" +
			"width=:width,height=:height,backgroundSound=:backgroundSound,backgroundRepeat=:backgroundRepeat,backgroundAlpha=:backgroundAlpha," +
			"sounLoop=:sounLoop,password=:password,hits=:hits,comments=:comments,isAuditing=:isAuditing," +
			"createDate=:createDate,modDate=:modDate,endPath=:endPath,background=:background,adWidth=:adWidth," +
			"adHeight=:adHeight,pagesCount=:pagesCount,pubmemSize=:pubmemSize,isValid=:isValid,isCommend=:isCommend," +
			"pubPath=:pubPath,pubType=:pubType,pubMagazineCount=:pubMagazineCount,pubZoom=:pubZoom,pubBackgroudSize=:pubBackgroudSize," +
			"pubadSize=:pubadSize,pwdLapseDate=:pwdLapseDate,pwdStartPage=:pwdStartPage,isCommpany=:isCommpany"+
			"WHERE id=:id";

	private final ParameterizedRowMapper<CorpPublication> mapper = new ParameterizedRowMapper<CorpPublication>() {
		public CorpPublication mapRow(ResultSet rs, int rowNum) throws SQLException {
			CorpPublication obj = new CorpPublication();
			obj.setId(rs.getInt("id"));
			obj.setTypeId(rs.getInt("typeId"));
			obj.setCorpId(rs.getString("corpId"));
			obj.setTitle(rs.getString("title"));
			obj.setThumbPath(rs.getString("thumbPath"));
			obj.setWidth(rs.getInt("width"));
			obj.setHeight(rs.getInt("height"));
			obj.setBackgroundSound(rs.getString("backgroundSound"));
			obj.setBackgroundRepeat(rs.getInt("backgroundRepeat"));
			obj.setBackgroundAlpha(rs.getInt("backgroundAlpha"));			
			obj.setSounLoop(rs.getInt("sounLoop"));
			obj.setPassword(rs.getString("password"));
			obj.setHits(rs.getInt("hits"));
			obj.setComments(rs.getInt("comments"));
			obj.setIsAuditing(rs.getInt("isAuditing"));
			obj.setCreateDate(rs.getTimestamp("createDate"));
			obj.setModDate(rs.getTimestamp("modDate"));
			obj.setEndPath(rs.getString("endPath"));
			obj.setBackground(rs.getString("background"));
			obj.setAdWidth(rs.getInt("adWidth"));
			obj.setAdHeight(rs.getInt("adHeight"));
			obj.setPagesCount(rs.getInt("pagesCount"));
			obj.setPubmemSize(rs.getInt("pubmemSize"));
			obj.setIsValid(rs.getInt("isValid"));
			obj.setIsCommend(rs.getInt("isCommend"));	
			obj.setPubPath(rs.getString("pubPath"));
			obj.setPubType(rs.getInt("pubType"));
			obj.setPubMagazineCount(rs.getInt("pubMagazineCount"));
			obj.setPubZoom(rs.getInt("pubZoom"));
			obj.setPubBackgroudSize(rs.getInt("pubBackgroudSize"));
			obj.setPubadSize(rs.getInt("pubadSize"));
			obj.setPwdLapseDate(rs.getTimestamp("pwdLapseDate"));
			obj.setPwdStartPage(rs.getInt("pwdStartPage"));
			obj.setIsCommpany(rs.getInt("isCommpany"));
			return obj;
		}
	};

	public List<CorpPublication> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public CorpPublication findById(int id) {
		List<CorpPublication> list = template.query(QUERY_ALL + " WHERE id=?",
				new Object[] { id }, mapper);
		if (list.size() == 0) {
			return null;
		} else
			return list.get(0);
	}
	
	public boolean isDeletePublication(String corpId, int publicationid) {
		boolean isDelete = false;
		try {
			String sql = "update Corp_Publication set isvalid=? where corpId = ? and id=? ";
			Object[] params = new Object[] {2, corpId,publicationid };
			template.update(sql, params);
			isDelete = true;
		} catch (Exception e) {
			
		}
		return isDelete;
	}

	
	public void insert(CorpPublication bean) {
		if(bean.getId()==null){
			bean.setId(new Integer(appSeqDao.getSequence("corp_publication_seq")));
		}			
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(CorpPublication bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map param){
		int count=0;
		String sql = QUERY_ALL_COUNT+" ";
		int len = param.size();
		Iterator<String> keyIt = param.keySet().iterator();
		Object values[] = new Object[len];
		int i=0;
		while(keyIt.hasNext()){
			String key = keyIt.next();
			if(i==0){
				sql+=" where " + key +"=? ";
			}else{
				sql+=" and " + key +"=? ";
			}
			values[i++]=param.get(key);
		}
		count = template.queryForInt(sql,values);
		return count;
	}
	

	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		String sql = QUERY_ALL+" ";
		
		int len = param.size();
		Iterator<String> keyIt = param.keySet().iterator();
		Object values[] = new Object[len+2];
		int i=0;
		while(keyIt.hasNext()){
			String key = keyIt.next();
			if(i==0){
				sql+=" where " + key +"=? ";
			}else{
				sql+=" and " + key +"=? ";
			}
			values[i++]=param.get(key);
		}
		values[len]=currentPage;
		values[len+1]=pageSize;
		List lists = template.query(sql + " limit ?,?",values, mapper);
		return lists;
		
	}
	
	
	
	@RemotingInclude
	public boolean updatePublicationCount(CorpPublication corpPublication) {
				boolean isUpdate = false;
			try {
				CorpPublication Publicationtemp = findById(corpPublication.getId());
				String sql = "update Corp_Publication set hits=? where id=?";
				Object[] params = new Object[] { Publicationtemp.getHits() + 1,
						Publicationtemp.getId() };
				template.update(sql, params);
				isUpdate = true;
			} catch (Exception e) {
				
			}
			return isUpdate;
	}
	
	
	public List<IdName> findIdNames(){
		List lists = template.query(ID_NAME, IdName.idNameMapper);
		return lists;
	}
	
	public CorpPublication getPubMagazineCount(String corpId) {
		List<CorpPublication> list = template.query(QUERY_ALL + " WHERE corpId=? and isValid=? ",
				new Object[] { corpId,1}, mapper);
		if (list.size() == 0) {
			return null;
		} else
			return list.get(0);
	}
	

	public boolean pubSizeAndIsauditingUpdate(int pubfilesize,
			int pubpagesize, int publicationid, boolean sign,int isauditing){
		boolean isUpdate = false;
		String sql = "";
		Object[] params = null;
		try {
			CorpPublication corpPublication = this.findById(publicationid);

			if (sign) {
				sql = "update Corp_Publication set pagescount=? ,pubmemsize=?,isauditing=? where id=?";
				params = new Object[] { pubpagesize, pubfilesize,isauditing,
						publicationid };
			} else {
				sql = "update Corp_Publication set pagescount=? ,pubmemsize=? where id=?";
				params = new Object[] {
						corpPublication.getPagesCount() - pubpagesize,
						corpPublication.getPubmemSize() - pubfilesize,
						publicationid };
			}
			template.update(sql, params);
			isUpdate = true;
		} catch (Exception e) {
			
		}
		return isUpdate;
	}
	
	
	public void audit(Long id, boolean pass){
		int aduiting=0;
		if(pass==true){
			aduiting=1;
		}
		template.update("update " + tableName+" set isAuditing=? where id=?", new Object[]{aduiting,id});
	}
}
